package pages;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import database.DBException;
import database.DBUtils;

import templates.Template;

/**
 * Servlet implementation class UsersList
 */
public class UsersList extends HttpServlet {
	private static final long serialVersionUID = 1L;

	private static final String templateName = "users_list.xml";
	private static final String rowTemplateName = "users_list_row.xml";
	private static final String tblTemplateName = "users_list_tbl";

	private static final XSLType xslType = XSLType.Users;
	private static final String pageTitle = "Users List";
	private static final String filterName = "users_list_filter";
	private static final int rowsOnPage = 10;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public UsersList() {
		super();
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		String filter = LMUtils.getSessionParamenterValue(request, filterName);
		if (filter.isEmpty()) {
			filter = "RESEARCHER";
		}

		int pageNow = 0;
		try {
			pageNow = Integer.parseInt(LMUtils
					.getRequestParameterValueNotEmpty(request, "p"));
		} catch (Exception e) {
			// nothing. pageNow = 0 anyway
		}

		try {
			Template template = new Template(templateName);
			template.addChange("users_list_rows", loadUsersListRows(request,
					filter, pageNow));

			// template = LMUtils.adminMenu(template);

			String xml = template.applyChanges();
			String xslName = LMUtils.getSessionParamenterValue(request,
					"xslName");
			String xsl = XSLT.applyXSL(xml, xslName, xslType);

			// creating users table from XSL output
			Template tableTemplate = new Template(tblTemplateName);
			// filter settings
			if (filter.equals("ADMIN")) {
				tableTemplate.addChange("selectFilterAdmin",
						"selected=\"selected\"");
				tableTemplate.addChange("selectFilterResearcher", "");
			} else {
				tableTemplate.addChange("selectFilterAdmin", "");
				tableTemplate.addChange("selectFilterResearcher",
						"selected=\"selected\"");
			}
			// pages
			tableTemplate.addChange("pages", LMUtils.pagesText("./UsersList",
					(Integer) request.getAttribute("totalPages"), pageNow));

			// xsl select
			tableTemplate.addChange("xsl_select", XSLT
					.getXSLs(xslType, xslName).applyChanges());

			tableTemplate
					.addChange("users_list_tbl", (xsl != null) ? xsl : xml);
			// tableTemplate = LMUtils.adminMenu(tableTemplate);
			response.getWriter().print(
					tableTemplate.createPage(pageTitle, LMUtils
							.getUserLogin(request)));

			/*
			 * response.getWriter().print( template.createPage("Users List",
			 * LMUtils .getUserLogin(request)));
			 */
			// response.getWriter().print(template.applyChanges());
		} catch (Exception e) {
			ErrorPage.printErrorPage(e.getMessage(), request, response);
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// filter
		try {
			String filter = LMUtils.getRequestParameterValueNotEmpty(request,
					"filter");
			// response.getWriter().print(filter);
			if (filter.equals("ADMIN")) {
				LMUtils.setSessionParamenterValue(request, filterName, "ADMIN");
			} else {
				LMUtils.setSessionParamenterValue(request, filterName,
						"RESEARCHER");
			}
		} catch (MissingParameterException e) {
			// no filter requested
		}

		// XSL
		try {
			LMUtils.setSessionParamenterValue(request, "xslName", LMUtils
					.getRequestParameterValueNotEmpty(request, "xslName"));
		} catch (MissingParameterException e) {
			// no XSL change was asked
		}

		doGet(request, response);
	}

	/**
	 * Fetches users info from DB ang generates XML from it
	 * 
	 * @param request
	 * @param filter
	 * @param pageNow
	 * @return String with XML
	 * @throws DBException
	 * @throws InternalErrorException
	 */
	private String loadUsersListRows(HttpServletRequest request, String filter,
			int pageNow) throws DBException, InternalErrorException {
		Template rowTemplate = new Template(rowTemplateName);
		String rowsText = "";
		Connection conn = null;
		PreparedStatement selectUsersStmt = null;
		ResultSet rs = null;
		PreparedStatement countStmt = null;
		ResultSet countRS = null;

		try {
			String usersTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.UsersTableName");
			String rolesTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.RolesTableName");

			conn = DBUtils.getConnection();
			String countQuery = "SELECT COUNT(A.`Login`) ";
			String query = "SELECT " + " A.`Login` as `login`"
					+ ", A.`Name` as `name`"
					+ ", A.`groupOwnerLogin` as `groupOwnerLogin`"
					+ ", B.`Name` as `groupName`"
					+ ", (C.`Role` = C.`RealRole`) as `isActive`";
			String whereQuery = "  FROM `"
					+ usersTableName
					+ "` as A , "
					+ usersTableName
					+ " as B, "
					+ rolesTableName
					+ " as C "
					+ " WHERE (A.`groupOwnerLogin` = B.`Login` OR A.`groupOwnerLogin` = '' AND B.`Login` = 'admin')"
					+ " AND A.`Login` = C.`Login` AND C.`RealRole` = ?";
			countQuery += whereQuery + ";";
			query += whereQuery + " ORDER BY A.`Login` LIMIT " + pageNow
					* rowsOnPage + ", " + rowsOnPage + ";";

			selectUsersStmt = conn.prepareStatement(query);
			selectUsersStmt.setString(1, filter);
			rs = selectUsersStmt.executeQuery();
			while (rs.next()) {
				rowTemplate.addChange("login", rs.getString("login"));
				rowTemplate.addChange("name", rs.getString("name"));
				rowTemplate.addChange("groupName", rs.getString("groupName"));
				rowTemplate.addChange("groupOwnerLogin", rs
						.getString("groupOwnerLogin"));
				boolean isActive = rs.getBoolean("isActive");
				rowTemplate.addChange("isActive", isActive ? "1" : "0");
				rowTemplate
						.addChange("activeColor", isActive ? "green" : "red");

				rowsText += rowTemplate.applyChanges();
			}

			// total pages
			countStmt = conn.prepareStatement(countQuery);
			countStmt.setString(1, filter);
			countRS = countStmt.executeQuery();
			countRS.next();
			request.setAttribute("totalPages", (int) Math.ceil(countRS
					.getFloat(1)
					/ rowsOnPage));
		} catch (SQLException e) {
			throw new DBException("Can't load Users List: " + e.getMessage(), e);
		} finally {
			DBUtils.close(countRS);
			DBUtils.close(countStmt);
			DBUtils.close(rs);
			DBUtils.close(selectUsersStmt);
			DBUtils.close(conn);
		}
		return rowsText;
	}
}
